﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Text;

namespace DataAccessLayer
{
    public class clsDatabase
    {
        #region Properties
        public clsSQLTranslation SQLTranslation { get; set; }
        public enum enumDatabase
        {
            eAccess2003, //TODO
            eAccess2007, //TODO
            eSQLServer2000, //TODO
            eSQLServer2005, //TODO
            eSQLServer2008, //TODO
            eMySQL //TODO
        }
        private enumDatabase Database { get; set; }
        private OleDbConnection Connection_OleDb { get; set; }
        private MySqlConnection Connection_MySql { get; set; }
        private String DbAddress { get; set; }
        private String DbPassword { get; set; }
        private String DbDatabaseName { get; set; }
        private String DbUserName { get; set; }
        #endregion

        #region Constructors
        /// <summary>
        /// Creates a connection to various databases for input and output.
        /// </summary>
        /// <param name="pDatabase">Database selection.</param>
        /// <param name="pDatabasePath">Path to the database.</param>
        public clsDatabase(enumDatabase pDatabase, String pDbAddress, String pDbUserName, String pDbPassword, String pDatabaseName)
        {
            this.Database = pDatabase;
            this.DbAddress = pDbAddress;
            this.DbUserName = pDbUserName;
            this.DbPassword = pDbPassword;
            this.DbDatabaseName = pDatabaseName;

            this.SQLTranslation = new clsSQLTranslation(pDatabase);
        }
        #endregion

        #region Events
        #endregion

        #region Public methods
        /// <summary>
        /// Requests a query.
        /// </summary>
        /// <param name="pQuery"></param>
        /// <returns>The requested table.</returns>
        public DataTable GetTable(clsQuery pQuery)
        {
            DataTable Table = new DataTable();
            Connect();
            if (this.Database == enumDatabase.eMySQL)
            {
                MySqlCommand Command = new MySqlCommand(pQuery.SQL, this.Connection_MySql);
                MySqlDataAdapter DataAdapter = new MySqlDataAdapter(Command);
                DataAdapter.Fill(Table);
            }
            else
            {
                OleDbCommand Command = new OleDbCommand(pQuery.SQL, this.Connection_OleDb);
                OleDbDataAdapter DataAdapter = new OleDbDataAdapter(Command);
                DataAdapter.Fill(Table);
            }
            return Table;
        }

        /// <summary>
        /// Executes a query.
        /// </summary>
        /// <param name="pQuery"></param>
        /// <returns>The number of affected rows.</returns>
        public Int32 ExecuteQuery(clsQuery pQuery)
        {
            Connect();
            if (this.Database == enumDatabase.eMySQL)
            {
                MySqlCommand Command = new MySqlCommand(pQuery.SQL, this.Connection_MySql);
                return Command.ExecuteNonQuery();
            }
            else
            {
                OleDbCommand Command = new OleDbCommand(pQuery.SQL, this.Connection_OleDb);
                return Command.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// Returns, whether with given parameters a connection to the database is estabilishable.
        /// </summary>
        /// <returns></returns>
        public Boolean IsConnectable()
        {
            return Connect();
        }

        /// <summary>
        /// Constructs a SQL request object.
        /// </summary>
        /// <param name="SQL">The SQL request object.</param>
        /// <returns></returns>
        public clsQuery Select(String pSQL, String pMessage)
        {
            clsQuery Query = new clsQuery(pSQL, pMessage);
            return Query;
        }
        #endregion

        #region Private methods
        /// <summary>
        /// Opens the connection to the database.
        /// </summary>
        /// <returns>The state of connection.</returns>
        private Boolean Connect()
        {
            try
            {
                if (this.Database == enumDatabase.eMySQL)
                {
                    if (this.Connection_MySql == null)
                    {
                        this.Connection_MySql = new MySqlConnection(GetConnectionString());
                    }

                    if (this.Connection_MySql.State != ConnectionState.Open)
                    {
                        this.Connection_MySql.Open();
                    }
                }
                else
                {
                    if (this.Connection_OleDb == null)
                    {
                        this.Connection_OleDb = new OleDbConnection(GetConnectionString());
                    }

                    if (this.Connection_OleDb.State != ConnectionState.Open)
                    {
                        this.Connection_OleDb.Open();
                    }
                }

                return true;
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// Generates the connection string.
        /// </summary>
        /// <returns>The connection string.</returns>
        private String GetConnectionString()
        {
            return GetConnectionString(this.DbAddress, this.DbPassword, this.DbUserName, this.DbDatabaseName);
        }

        /// <summary>
        /// Generates the connection string with given parameters.
        /// </summary>
        /// <param name="DbAddress">Database address.</param>
        /// <param name="DbPassword">Database password.</param>
        /// <param name="DbUserName">Database username.</param>
        /// <param name="DbDatabaseName">Database name.</param>
        /// <returns></returns>
        private String GetConnectionString(String DbAddress, String DbPassword, String DbUserName, String DbDatabaseName)
        {
            String ConnectionString = "";
            
            switch (Database)
            {
                case enumDatabase.eAccess2003:
                    ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;";
                    if (DbAddress != "")
                    {
                        ConnectionString += "Data Source=" + DbAddress + ";";
                    }
                    if (DbPassword != "")
                    {
                        ConnectionString += "Jet OLEDB:Database Password=" + DbPassword + ";";
                    }
                    break;
                case enumDatabase.eAccess2007:
                    ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;";
                    if (DbAddress != "")
                    {
                        ConnectionString += "Data Source=" + DbAddress + ";";
                    }
                    if (DbPassword != "")
                    {
                        ConnectionString += "Jet OLEDB:Database Password=" + DbPassword + ";";
                    }
                    break;
                case enumDatabase.eMySQL:
                    //ConnectionString = "Provider=MySQLProv;";
                    if (DbAddress != "")
                    {
                        ConnectionString += "Data Source=" + DbAddress + ";";
                    }
                    if (DbDatabaseName != "")
                    {
                        ConnectionString += "Database=" + DbDatabaseName + ";";
                    }
                    if (DbUserName != "")
                    {
                        ConnectionString += "User Id=" + DbUserName + ";";
                    }
                    if (DbPassword != "")
                    {
                        ConnectionString += "Password=" + DbPassword + ";";
                    }
                    break;
                case enumDatabase.eSQLServer2000:
                    ConnectionString = "Provider=sqloledb;";
                    if (DbAddress != "")
                    {
                        ConnectionString += "Data Source=" + DbAddress + ";";
                    }
                    if (DbDatabaseName != "")
                    {
                        ConnectionString += "Initial Catalog=" + DbDatabaseName + ";";
                    }
                    if (DbUserName != "")
                    {
                        ConnectionString += "User Id=" + DbUserName + ";";
                    }
                    if (DbPassword != "")
                    {
                        ConnectionString += "Password=" + DbPassword + ";";
                    }
                    break;
                case enumDatabase.eSQLServer2005:
                    ConnectionString = "Provider=SQLNCLI10;";
                    if (DbAddress != "")
                    {
                        ConnectionString += "Server=" + DbAddress + ";";
                    }
                    if (DbDatabaseName != "")
                    {
                        ConnectionString += "Database=" + DbDatabaseName + ";";
                    }
                    if (DbUserName != "")
                    {
                        ConnectionString += "Uid=" + DbUserName + ";";
                    }
                    if (DbPassword != "")
                    {
                        ConnectionString += "Pwd=" + DbPassword + ";";
                    }
                    break;
                case enumDatabase.eSQLServer2008:
                    ConnectionString = "Provider=SQLNCLI10;";
                    if (DbAddress != "")
                    {
                        ConnectionString += "Server=" + DbAddress + ";";
                    }
                    if (DbDatabaseName != "")
                    {
                        ConnectionString += "Database=" + DbDatabaseName + ";";
                    }
                    if (DbUserName != "")
                    {
                        ConnectionString += "Uid=" + DbUserName + ";";
                    }
                    if (DbPassword != "")
                    {
                        ConnectionString += "Pwd=" + DbPassword + ";";
                    }
                    break;
                default:
                    throw new ArgumentException("No database given.");
            }

            return ConnectionString;
        }
        #endregion
    }
}
